create database InventoryManagement
go
use InventoryManagement

-- CREATE TABLE --
go
--drop table User_System
create table User_System
(
	User_ID int primary key identity(1,1),
	User_Name varchar(20) not null unique,
	Priority int not null,
	Password  varchar(50) not null default '123456',
	First_Name varchar(50),
	Last_Name varchar(50),
	Gender char(1) check (Gender in ('F','M')),
	Email varchar(50),
	Birthday datetime,
	Phone varchar(13),
)
go
--drop table Category
create table Category
(
	Category_ID int primary key identity(1,1),
	Category_Name varchar(50) unique
)
go
create table Item
(
	Item_ID int primary key identity(1,1),
	Item_Name varchar(50) not null,
	Quantity int not null,
	Unit varchar(20) not null,
	Price float not null,
	Category_ID int foreign key references Category(Category_ID)
)
go
--Drop table Bill
create table Bill
(
	Bill_ID int primary key identity(1,1),
	[Date] datetime default getdate(),
	[User_ID] int foreign key references User_System([User_ID]),
	[Status] int default(0)
)
go
alter table Bill
add constraint Bill_Unique Unique (Bill_ID,[Date],[User_ID])

-- Drop table Bill_Detail
go
create table Bill_Detail
(
	Bill_ID int foreign key references Bill(Bill_ID) not null,
	Item_ID int foreign key references Item(Item_ID) not null,
	Quantity int not null,
	Price float not null
)
go
alter table Bill_Detail
add constraint BillDetail_PK primary key (Bill_ID,Item_ID)
go
create table Receipt
(
	Bill_ID int foreign key references Bill(Bill_ID) not null,
	Item_ID int foreign key references Item(Item_ID) not null,
	User_ID int foreign key references User_System(User_ID) not null,
	Quantity int not null,
	[Date] Datetime default(getDate())
)
go
alter table Receipt add constraint Receipt_PK primary key (Bill_ID,Item_ID)

go
--drop table Report
create table Report
(
	Report_ID int primary key identity(1,1),
	Sender_ID int foreign key references User_System(User_ID),
	Receiver_ID int foreign key references User_System(User_ID),
	Message varchar(500),
	Status int default(0),
	[Date] datetime default(getdate())
)
select * from User_System
-- END CREATE TABLE --

-- INSERT DATA --
go
insert into User_System values('admin',0,'123456','admin','admin','M','adminInventoryManagement@gmail.com','5/28/1991','01683333160')
go
insert into Category values('Fruit and vegetable')
insert into Category values('Freeze Food')
go
insert into Item values('Apple','100','Kilogram',16.3,1)
insert into Item values('Pork','76','Kilogram',13.2,2)
insert into Item values('Pomelo','82','Kilogram',6.3,1)
go
set Identity_Insert Bill On
insert into Bill(Bill_ID,User_ID) values(4,4)
insert into Bill(Bill_ID,Date,User_ID,Status) values(3,'1/17/2012',4,2)
insert into Bill(Bill_ID,Date,User_ID,Status) values(5,'1/17/2011',4,1)
insert into Receipt values(1,1,4,2,GETDATE())
-- END INSERT DATA --

-- CREATE PROCEDURE --
---------- PROCEDURE OF USER_SYSTEM --------
--drop proc Get_User
go
create proc Get_User
as
	select * from User_System
go
create proc Change_Password
(
	@user_id int,
	@password varchar(50)
)
as
	begin
		update User_System
		set Password = @password
		where User_ID = @user_id
	end

--drop proc Login
go
create proc Login
	@user_name varchar(20),
	@password varchar(50)
as
	select * from User_System
	where User_Name like @user_name and Password like @password

--drop proc Add_User
go
create proc Add_User
(
	@user_id int out,
	@user_name varchar(20),
	@priority int,
	@password varchar(50),
	@first_name varchar(50),
	@last_name varchar(50),
	@gender char(1),
	@email varchar(50),
	@birthday datetime,
	@phone varchar(13)
)
as
	begin
		insert into User_System values(@user_name,@priority,@password,@first_name,@last_name,@gender,@email,@birthday,@phone)		
		set @user_id = @@IDENTITY
	end
go
create proc Select_User_ID
	@username varchar(50)
as
	select User_ID from User_System where User_Name = @username
go
create proc Get_User_By_Priority
	@priority int
	as
	select * from User_System where Priority = @priority
go
create proc Select_User_By_ID
	@userID int
	as
	select * from User_System where User_ID = @userID

----------- END PROCEDURE OF USER_SYSTEM -------

------------ PROCEDURE OF ITEM ---
go
create proc Select_Item
as
	select * from Item inner join Category
	on Item.Category_ID = Category.Category_ID
	order by Item_Name
go
--drop proc Select_Item_By_Name
create proc Select_Item_By_Name 
	@name varchar(50)
as
	select * from Item inner join Category
	on Item.Category_ID = Category.Category_ID
	where Item_Name like  @name +'%'
go
create proc Select_Item_By_Category_And_Item_Name
	@itemName varchar(50),
	@categoryName varchar(50)
as
	select Item_ID,Item_Name, Quantity,Price,Category_Name,Unit from Item inner join Category
	on Item.Category_ID = Category.Category_ID
	where Item_Name like @itemName and Category_Name like @categoryName
go
create proc Select_Item_By_Category_ID
	@category_id int
as
	select *from Item inner join Category
	on Item.Category_ID = Category.Category_ID
	where Item.Category_ID = @category_id
go
create proc Add_Item
(
	@id int out,
	@name varchar(50),
	@quantity int,
	@unit varchar(20),
	@price float,
	@categoryName varchar(50)
)
as
	begin
		declare @categoryID int
		select @categoryID = Category_ID from Category where Category_Name like @categoryName
		
		declare @temp varchar(50) = ''
		select @temp = Item_Name from Item where Item_Name like @name
		
		if @temp like ''
			begin
				insert into Item values(@name,@quantity,@unit,@price,@categoryID)
				set @id = @@IDENTITY
			end
		else
			set @id = -1
	end

go
create proc Update_Price_Item(@id int,@newPrice float)
as
	begin
		update Item
		set Price = @newPrice
		where @id = Item_ID
	end
go
create proc Select_Item_By_ID
	@itemID int
	as
	select * from Item where Item_ID = @itemID	
------------- END PROCEDURE OF ITEM-------
	
------------- PROCEDURE OF BILL ---------
go
create proc Add_Bill
	@billid int output,
	@userid int
as
	begin
	insert into Bill values(default,@userid,default)
	set @billid =  @@IDENTITY
	end
go
create proc Add_Bill_Detail
	@billid int,
	@itemid int,
	@quantity int,
	@price float
	as
	begin 
	insert into Bill_Detail values(@billid,@itemid,@quantity,@price)
	update Item set Quantity = Quantity - @quantity where Item_ID = @itemid
	end
go
create proc Remove_Bill_Detail
	@billid int,
	@itemid int,
	@quantity int
as
	begin
	delete Bill_Detail where Bill_ID = @billid and Item_ID = @itemid
	update Item set Quantity = Quantity + @quantity where Item_ID = @itemid
	end
go
create proc Update_Bill_Detail
	@billid int,
	@itemid int,
	@quantity int
as
	begin
	update Item set Quantity = Quantity - @quantity where Item_ID = @itemid
	update Bill_Detail set Quantity = Quantity + @quantity where Bill_ID = @billid
	end
go
create proc Select_All_Bill_Detail
as
	select Bill_ID,Bill_Detail.Item_ID,Item_Name,Category_Name,Bill_Detail.Quantity, Bill_Detail.Price from Bill_Detail inner join Item
	on Bill_Detail.Item_ID = Item.Item_ID
	inner join Category 
	on Item.Category_ID = Category.Category_ID
go
create proc Change_Status_Bill
	@billID int,
	@status int
	as
	update Bill set Status = @status where Bill_ID = @billID
go
create proc Select_Bill_ID
	@userid int,
	@date Datetime
as
	select Bill_ID from Bill where User_ID = @userid and Date = @date
go
create proc Select_Bill
as
	select Bill_ID,Date,Bill.User_ID,User_Name,First_Name,Last_Name,Status from Bill inner join User_System
	on Bill.User_ID = User_System.User_ID
	order by Date desc

go
create proc Total_Price
	@id int
as
	select sum(Price*Quantity)  as Total from Bill_Detail where Bill_ID = @id
go
create proc Select_Bill_Detail
	@id int
as
	select Bill_Detail.Item_ID,Bill_ID,Item_Name,Category_Name,Bill_Detail.Quantity, Bill_Detail.Price from Bill_Detail inner join Item
	on Bill_Detail.Item_ID = Item.Item_ID
	inner join Category 
	on Item.Category_ID = Category.Category_ID
	where Bill_ID = @id
go
create proc Select_Bill_By_ID
	@billID int
	as
	select User_Name, First_Name,Last_Name,Date,Status from Bill
	inner join User_System on Bill.User_ID = User_System.User_ID
	where Bill_ID = @billID
	
go
create proc Remove_Bill
	@billID int
	as
	Delete Bill where Bill_ID = @billID
go
create proc Select_Bill_By_User
	@userID int
	as
	select Bill_ID,Date,Bill.User_ID,User_Name,First_Name,Last_Name,Status from Bill 
	inner join User_System
	on Bill.User_ID = User_System.User_ID
	where User_System.User_ID=@userID
go
create proc Cancel_BillDetail
	@itemID int,
	@quantity int
	as
	update Item set Quantity = Quantity + @quantity where Item_ID = @itemID

------------ END PROCEDURE OF BILL --------

----------- PROCEDURE OF CATEGORY ------------
go
create proc Select_Category
as
	select * from Category
go
create proc Select_Category_ID
	@category_name varchar(50)
as
	select * from Category where Category_Name = @category_name
go
--drop proc Add_Category
create proc Add_Category(@id int out, @categoryName varchar(50))
as
	begin
		if @categoryName != ''
			begin
				insert into Category values(@categoryName)
				set @id = @@IDENTITY
			end
		else
			set @id = -1			
	end
go 
create proc Select_Category_By_Item_Name
	@itemName varchar(50)
	as
	select Category.Category_ID, Category_Name from Category 
	inner join Item
	on Item.Category_ID = Category.Category_ID
	where Item_Name = @itemName
go
create proc Select_Category_By_ItemName_BillID
	@itemName varchar(50),
	@billID int
	as
	select Category.Category_ID,Category_Name from Item inner join Category 
	on Item.Category_ID = Category.Category_ID
	inner join Bill_Detail
	on Bill_Detail.Item_ID = Item.Item_ID
	where Item_Name = @itemName and Bill_ID = @billID
------------ END PROCEDURE OF CATEGORY -------------
	
------------ PROCEDURE OF RECEIPT ---------------
go
-- drop proc Add_Receipt
create proc Add_Receipt
	@billID int,
	@itemID int,
	@userID int,
	@quantity int
	as
	insert into Receipt values(@billID,@itemID,@userID,@quantity,default)
go
create proc Select_Receipt_By_BillID_ItemID
	@billID int,
	@itemID int
	as
	select Receipt.User_ID, Date,Receipt.Quantity,User_Name,Item_Name,Category_Name
	from User_System inner join Receipt
	on User_System.User_ID = Receipt.User_ID
	inner join Item on Item.Item_ID = Receipt.Item_ID
	inner join Category on Category.Category_ID = Item.Category_ID
	where Receipt.Bill_ID = @billID and Receipt.Item_ID = @itemID
go
create proc Select_Receipt_By_User
	@userID int
as
	select Item.Item_ID,Receipt.Bill_ID, Date,Receipt.Quantity,User_Name,Item_Name,Category_Name
	from User_System inner join Receipt
	on User_System.User_ID = Receipt.User_ID
	inner join Item on Item.Item_ID = Receipt.Item_ID
	inner join Category on Category.Category_ID = Item.Category_ID
	where Receipt.User_ID = @userID
go
create proc Select_Receipt
as
	select Item.Item_ID,Receipt.Bill_ID, Date,Receipt.Quantity,User_Name,Item_Name,Category_Name
	from User_System inner join Receipt
	on User_System.User_ID = Receipt.User_ID
	inner join Item on Item.Item_ID = Receipt.Item_ID
	inner join Category on Category.Category_ID = Item.Category_ID
go
create proc Update_Reciept
	@billID int,
	@itemID int,
	@quantity int
as
	update Receipt set Quantity = @quantity where Bill_ID = @billID and Item_ID = @itemID

----------- END PROCEDURE OF RECEIPT --------------
----------- PROCEDURE OF REPORT -------------------
go
create proc Add_Report
	@senderID int,
	@reveiceID int,
	@messages varchar(500)
	as
	Insert into Report values(@senderID,@reveiceID,@messages,default,default)
go
create proc Select_Report_By_ReceiverID
	@userID int
	as
	select * from Report where Receiver_ID = @userID
	order by Date desc
go
create proc Change_Status_Report
	@reportID int
	as
	Update Report set Status = 1 where Report_ID = @reportID
----------- END PROCEDURE OF REPORT ---------------
go
create proc Select_Quantity_By_ItemID_BillID
	@itemID int,
	@billID int
	as
	select Quantity from Bill_Detail 
	where Item_ID = @itemID and Bill_ID = @billID
go
create proc Statistics_By_Date
	@beginDate Date,
	@endDate Date
	as
	Select Item_ID,Quantity = SUM(Quantity),Total = SUM(Quantity*Price) from Bill_Detail
	inner join Bill on Bill.Bill_ID = Bill_Detail.Bill_ID
	where Bill.Date >= @beginDate and Bill.Date <= @endDate
	group by Bill_Detail.Item_ID
	Order by Total DESC
	
-- END CREATE PROCEDURE --